package com.ht.dao;

import com.ht.bean.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

public interface EnrollmentDao {
    //招生模块
    //查询Enrollment表
    @Select({"<script>",
            "SELECT et.enrollmentId,et.studName,et.card,et.amount,et.reviewStatus,et.enrollMoney,et.status,et.sex,et.tell,et.qq,et.school,et.classes,et.score,et.computer,et.testTime,et.studType,et.startTime,et.signdate,et.testTime,et.remark,et.paymentTime,et.negativeName,et.reviewer,et.reviewerTime,c.classTypeName,mr.majorName from enrollment et,major mr,classtype c where et.majorId=mr.majorid and et.studType = c.calssTypeId",
            "<if test=\"et.tell!=null and et.tell!=''\">",
            " and tell like concat('%',#{et.tell},'%')",
            "</if>",
            "<if test=\"et.studName!=null and et.studName!=''\">",
            " and studName like concat('%',#{et.studName},'%')",
            "</if>",
            "<if test=\"et.negativeName!=null and et.negativeName!=''\">",
            " and negativeName like concat('%',#{et.negativeName},'%')",
            "</if>",
            "<if test=\"et.majorId!=null and et.majorId!=''\">",
            " and et.majorId =#{et.majorId}",
            "</if>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
    public List<Map> etlist(@Param("startIndex") int  startIndex, @Param("pageSize") int pageSize, @Param("et") Enrollment et);
    //查询总行数
    @Select({"<script>",
            "SELECT count(*) from enrollment et,major mr,classtype c where et.majorId=mr.majorid and et.studType = c.calssTypeId",
            "<if test=\"et.tell!=null and et.tell!=''\">",
            " and tell like concat('%',#{et.tell},'%')",
            "</if>",
            "<if test=\"et.studName!=null and et.studName!=''\">",
            " and studName like concat('%',#{et.studName},'%')",
            "</if>",
            "<if test=\"et.negativeName!=null and et.negativeName!=''\">",
            " and negativeName like concat('%',#{et.negativeName},'%')",
            "</if>",
            "<if test=\"et.majorId!=null and et.majorId!=''\">",
            " and et.majorId =#{et.majorId}",
            "</if>",
            "</script>"})
    public int countet(@Param("et") Enrollment et);

    //查询专业表
    @Select("select * from major")
    public List<Major> allmajor();

    //查询班级类别表
    @Select("select * from classtype")
    public List<Classtype> allclasstype();

    //新增
    @Insert("insert into enrollment values(null,#{studName},#{card},#{sex},#{tell},#{qq},#{school},#{classes},#{amount},#{computer},#{testTime},#{startTime},#{signdate},#{empId},#{status},#{remark},#{studType},#{paymentTime},#{score},#{enrollMoney},#{enrollMoneyTime},#{reviewStatus},#{negativeName},#{reviewer},#{reviewerTime},#{majorId})")
    boolean etadd(Enrollment enrollment);

    //预定报名费
    @Update("update enrollment set amount=#{amount} where enrollmentId=#{enrollmentId}")
    boolean ydbmf(Enrollment enrollment);

    //预定报名费审核
    @Update("update enrollment set reviewStatus=#{reviewStatus} where enrollmentId=#{enrollmentId}")
    boolean ydbmfsh(Enrollment enrollment);

    //提成金额
    @Update("update enrollment set enrollMoney=#{enrollMoney} where enrollmentId=#{enrollmentId}")
    boolean tcje(Enrollment enrollment);

    //试学
    @Update("update enrollment set testTime=#{testTime} where enrollmentId=#{enrollmentId}")
    boolean sx(Enrollment enrollment);

    //学生状态  为试学/流失
    @Update("update enrollment set status=#{status} where enrollmentId=#{enrollmentId}")
    boolean wsx(Enrollment enrollment);


    //查询id
    @Select("select * from enrollment where enrollmentId=#{enrollmentId}")
    public Enrollment cxid(@Param("enrollmentId") Integer enrollmentId);


    //删除
    @Delete("Delete from enrollment where enrollmentId=#{enrollmentId}")
    boolean delet(String enrollmentId);

    //查询要修改的id
    @Select("select * from enrollment where enrollmentId=#{enrollmentId}")
    public List<Enrollment> selbyid(String enrollmentId);

    //修改
    @Update("update enrollment set studName=#{studName},card=#{card},sex=#{sex},tell=#{tell},qq=#{qq},school=#{school},classes=#{classes},amount=#{amount},computer=#{computer},testTime=#{testTime},startTime=#{startTime},signdate=#{signdate},empId=#{empId},status=#{status},remark=#{remark},studType=#{studType},paymentTime=#{paymentTime},score=#{score},enrollMoney=#{enrollMoney},enrollMoneyTime=#{enrollMoneyTime},reviewStatus=#{reviewStatus},negativeName=#{negativeName},reviewer=#{reviewer},reviewerTime=#{reviewerTime},majorId=#{majorId} where enrollmentId=#{enrollmentId}")
     public boolean updet(Enrollment enrollment);

    //-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    //文件管理
    //查询
    @Select({"<script>",
            "select dc.docId,dc.docName,dc.url,dc.optime,dc.remark,e.empName from datadoc dc,emp e where dc.empId=e.empId",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
    public List<Map> alldc(@Param("startIndex") int  startIndex, @Param("pageSize") int pageSize, @Param("dc") Datadoc datadoc);
    //查询总行数
    @Select("SELECT count(*) from datadoc dc,emp e where dc.empId=e.empId")
    public int counte(@Param("dc") Datadoc datadoc);

    //查询员工表
    @Select("select * from emp")
    public List<Emp> allemp();

    //新增
    @Insert("insert into datadoc values(null,#{docName},#{url},NOW(),#{remark},#{empId})")
    public boolean dcadd(Datadoc datadoc);

    //删除
    @Delete("delete from datadoc where docId=#{docId}")
    boolean dcdel(String docId);

    //根据id查询文件名
    @Select("select * from datadoc where docId=#{docId}")
    public Datadoc XzDatadoc(String docId);

    //--------------------------------------------------------------------
    //系统报表
    //招生统计
    //查询Enrollment表
    @Select({"<script>",
            "select et.enrollmentId,et.negativeName,mr.majorName,count(*)ren,sum(et.enrollMoney)enrollMoney from enrollment et,major mr where et.majorId=mr.majorid group by et.negativeName,mr.majorName",
            "<if test=\"et.negativeName!=null and et.negativeName!=''\">",
            " and et.negativeName like concat('%',#{et.negativeName},'%')",
            "</if>",
            "<if test=\"et.majorId!=null and et.majorId!=''\">",
            " and et.majorId =#{et.majorId}",
            "</if>",
            " limit #{startIndex},#{pageSize}",

            "</script>"})
    public List<Map> bblist(@Param("startIndex") int  startIndex, @Param("pageSize") int pageSize, @Param("et") Enrollment et);
    //查询总行数
    @Select({"<script>",
            "select count(*) from (",
            "select et.enrollmentId,et.negativeName,mr.majorName,count(*)ren,sum(et.enrollMoney)enrollMoney from enrollment et,major mr where et.majorId=mr.majorid group by et.negativeName,mr.majorName",
            "<if test=\"et.negativeName!=null and et.negativeName!=''\">",
            " and et.negativeName like concat('%',#{et.negativeName},'%')",
            "</if>",
            "<if test=\"et.majorId!=null and et.majorId!=''\">",
            " and et.majorId =#{et.majorId}",
            "</if>",
            " )a",

            "</script>"})
    public int countb(@Param("et") Enrollment et);

    //查询招生列表   整图形
    @Select("select et.negativeName,count(*)ren from enrollment et,major mr where et.majorId=mr.majorid group by et.negativeName,mr.majorName")
    public List<Map> txlist();


    //预定报名费统计
    @Select({"<script>",
            "select enrollmentId,studName,amount,status,negativeName from enrollment ",
            "<if test=\"et.studName!=null and et.studName!=''\">",
            " and studName like concat('%',#{et.studName},'%')",
            "</if>",
            " limit #{startIndex},#{pageSize}",
            "</script>"})
    public List<Map> fylist(@Param("startIndex") int  startIndex, @Param("pageSize") int pageSize, @Param("et") Enrollment et);
    //查询总行数
    @Select({"<script>",

            "select  count(*) from enrollment ",
            "<if test=\"et.studName!=null and et.studName!=''\">",
            " and studName like concat('%',#{et.studName},'%')",
            "</if>",

            "</script>"})
    public int countf(@Param("et") Enrollment et);


}
